﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Data;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;
using SdmxMl.GenericMetadata;

namespace SdmxMl.SmsImportCfg
{
    /// <summary>
    /// Import on predefined basic template, multiples codelists
    /// </summary>
    public class BasicCodelistsImport
    {
        private SmManager sm;
        private string urnCodelist;

        #region Excel Support

        private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;

        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }
        #endregion

        public void Import(string excelFile, string agencyId, int headerCount)
        {
            sm = ReferenceManager.GetSmContainerImage();

            string currentCodelistId = string.Empty;

            // Prepare Excel reader
            spreadsheetConcept = LoadSources(excelFile);
            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);

            string codelistId = null;
            CodeListType clt = null;

            // scan rows
            for (int row = headerCount + 1; row <= dt.Rows.Count; row++)
            {
                int cellIndex = 1;
                // Get codelist ID
                currentCodelistId = wh.GetCellContent(row, cellIndex++);

                // If new id, complete codelist, check if already defined, if not add to container
                if (currentCodelistId != codelistId)
                {
                    // Finalize previous codelist
                    if (codelistId != null)
                    {
                    }

                    // Create new one
                    clt = new CodeListType();
                    clt.Id = "CL_" + currentCodelistId;
                    clt.AgencyId = agencyId;
                    clt.Name.UpdateForLanguage("de", "definiert werden");
                    sm.PutArtefact(clt.ArType, clt);
                    codelistId = currentCodelistId;
                }

                //Get code, and text in lang
                CodeType c = new CodeType();
                c.Id = wh.GetCellContent(row, cellIndex++);
                c.Name.UpdateForLanguage("de", wh.GetCellContent(row, cellIndex++));
                c.Name.UpdateForLanguage("fr", wh.GetCellContent(row, cellIndex++));
                clt.CodeList.Add(c);
            }

            object ob = NavigationManager.LoadFromSmManager(sm);

        }

        public void ImportVarCatalog(string excelFile, string agencyId, int headerCount)
        {
            // Prepare Excel reader
            spreadsheetConcept = LoadSources(excelFile);
            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);
            sm = new SmManager();
            ConceptSchemeType cst = new ConceptSchemeType();
            cst.Id = "CS_EFG_VAR";
            cst.AgencyId = agencyId;
            cst.Name.UpdateDefaultText("definiert werden");
            sm.PutArtefact(cst.ArType, cst);
            // scan rows
            for (int row = headerCount + 1; row <= dt.Rows.Count; row++)
            {
                int cellIndex = 1;
                ConceptType c = new ConceptType();
                cst.ConceptList.Add(c);
                c.Id = wh.GetCellContent(row, cellIndex++).ToUpper();
                c.Name.UpdateForLanguage("de", wh.GetCellContent(row, cellIndex++));
                c.Name.UpdateForLanguage("fr", wh.GetCellContent(row, cellIndex++));
                if (c.Name.IsEmpty)
                    c.Name.UpdateDefaultText("definiert werden");

                c.TextFormat.maxLength = wh.GetCellContent(row, cellIndex++);
                string typeCd = wh.GetCellContent(row, cellIndex++);
                switch (typeCd)
                {
                    case "1":
                        c.TextFormat.TextType = TextTypeType.String; break;
                    case "2":
                        c.TextFormat.TextType = TextTypeType.Integer; break;
                    default:
                        c.TextFormat.TextType = TextTypeType.Undefined; break;
                }
                string codeFmt = wh.GetCellContent(row, cellIndex++);
                if (codeFmt.Length > 0)
                {
                    c.CoreRepresentation.AgencyId = agencyId;
                    c.CoreRepresentation.Id = "CL_" + codeFmt;
                }

                string questType = wh.GetCellContent(row, cellIndex++);

                string question = wh.GetCellContent(row, cellIndex++);
                if (question.Length > 0)
                {
                    Annotation a = new Annotation("QUEST_ID");
                    a.Title = question;
                    for (int i = 0; i < 3; i++)
                    {
                        question = wh.GetCellContent(row, cellIndex++);
                        if (question.Length > 0)
                            a.Title += "_" + question;
                    }
                    c.AnnotationList.Add(a);
                }
            }
            NavigationManager.LoadFromSmManager(sm);
        }

        public void Adjust()
        {
            SmManager sm = SdmxMl.Manager.ReferenceManager.GetSmContainerImage();
            ArtefactList arl = sm.GetArtefactList(SdmxArtefactType.CodeLists);
            foreach (CodeListType clt in arl)
            {
                if (clt.Id.EndsWith("_"))
                    clt.Id += "U";
            }
            ConceptSchemeType cst = sm.GetArtefactList(SdmxArtefactType.Concepts)[0] as ConceptSchemeType;
            foreach (ConceptType c in cst.ConceptList)
            {
                c.Id = c.Id.ToUpper();
                if (c.CoreRepresentation.Id.EndsWith("_"))
                {
                    c.CoreRepresentation.Id += "U";
                }
                Annotation a = c.AnnotationList.GetAnnotationType("QUEST_ID");
                if (a != null)
                {
                    a.Type = "QUEST_PRINT";
                    string[] ql = a.Title.Split('_');
                    string quest = string.Empty;
                    foreach (string s in ql)
                    {
                        string q = s.Trim();
                        string dec = q.Substring(s.Length - 2);
                        q = q.Substring(0, s.Length - 2) + "." + dec;
                        if (quest.Length > 0)
                            quest += '_';
                        quest += q;
                    }
                    a.Title = quest;

                }

            }
        }


        #region Compare codelists

        /// <summary>Check if a codelist of the cache can satisfy the proposed one</summary>
        /// <param name="newCodelist">Proposed codelist</param>
        /// <returns>A codelist of the cache that satisfy proposed one or null if none.</returns>
        private CodeListType CodelistAlreadyDefined(CodeListType newCodelist)
        {
            ArtefactManager amgr = sm.GetArtefactManager(SdmxArtefactType.CodeLists);
            foreach (CodeListType clt in amgr.ArtefactList)
                if (SameCodes(newCodelist.CodeList, clt.CodeList))
                    return clt;
            return null;
        }

        /// <summary>Check id content of the two proposed codelists is the same</summary>
        /// <param name="a"></param> <param name="b"></param>
        /// <returns>True if same, false otherwise</returns>
        private bool SameCodes(CodeList a, CodeList b)
        {
            bool sameCodes = false;
            if (a.Count == b.Count)
            {
                sameCodes = true;
                for (int c = 0; c < a.Count; c++)
                {
                    if (a[c].Id != b[c].Id)
                    {
                        sameCodes = false;
                        break;
                    }
                    else
                    {
                        foreach (string lg in LangSupport.SupportedLanguages)
                        {
                            if (lg != "de" && lg != "fr")
                                continue;
                            if (a[c].Name.GetForLanguage(lg) != b[c].Name.GetForLanguage(lg))
                            {
                                sameCodes = false;
                                break;
                            }
                        }
                        if (sameCodes == false)
                            break;
                    }
                }
            }

            return sameCodes;
        }
        #endregion

        public void SplitSchemes(string excelFile)
        {
            SmManager sm = SdmxMl.Manager.ReferenceManager.GetSmContainerImage();
            ConceptSchemeType cstOrg = sm.GetArtefact(SdmxArtefactType.Concepts, new ArtefactRefBase("CH1_EFG+CS_EFG_VAR+1.0")) as ConceptSchemeType;
            ConceptSchemeType cstOne = sm.GetArtefact(SdmxArtefactType.Concepts, new ArtefactRefBase("CH1_EFG+CS_TAB_TYP1+1.0")) as ConceptSchemeType;
            ConceptSchemeType cstTwo = sm.GetArtefact(SdmxArtefactType.Concepts, new ArtefactRefBase("CH1_EFG+CS_TAB_TYP2+1.0")) as ConceptSchemeType;
            cstTwo.ConceptList.HasHierarchy = true;
            cstOne.ConceptList.HasHierarchy = true;

            spreadsheetConcept = LoadSources(excelFile);
            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);

            HashSet<string> variableSet = new HashSet<string>();
            for (int row = 2; row <= dt.Rows.Count; row++)
            {
                int cellIndex = 1;
                string var = wh.GetCellContent(row, cellIndex++).ToUpper().Trim();
                if (variableSet.Contains(var))
                    throw new ApplicationException("Var already treated: " + var);
                variableSet.Add(var);

                string module = wh.GetCellContent(row, cellIndex++);
                if (module.Length > 1)
                {
                    module = "M" + module;
                }
                else
                {
                    module = "M0" + module;
                }
                string table = wh.GetCellContent(row, cellIndex++);
                ConceptSchemeType cst = table == "1" ? cstOne : cstTwo;

                // Does concept exists in original
                ConceptType c = cstOrg.ConceptList.LocateItem(var) as ConceptType;
                if (c == null)
                {
                    c = new ConceptType();
                    c.Id = var;
                    c.Name.UpdateDefaultText("Not in original set");
                }

                c.Parent = module;
                cst.ConceptList.Add(c);
            }
        }

        public void CompleteQuest()
        {
            SmManager sm = SdmxMl.Manager.ReferenceManager.GetSmContainerImage();

            ConceptSchemeType cstOne = sm.GetArtefact(SdmxArtefactType.Concepts, new ArtefactRefBase("CH1_EFG+CS_TAB_TYP1+2013.0")) as ConceptSchemeType;
            ConceptSchemeType cstTwo = sm.GetArtefact(SdmxArtefactType.Concepts, new ArtefactRefBase("CH1_EFG+CS_TAB_TYP2+2013.0")) as ConceptSchemeType;

            MetadataSetType metaset = sm.GetArtefact(SdmxArtefactType.MetadataSet, new ArtefactRefBase("CH1_EFG+MS_Q_EFG+2013.0")) as MetadataSetType;

            foreach (AttributeValueSetType atv in metaset.AttributeValueSetList)
            {
                urnCodelist = string.Empty;
                string questId = atv.TargetValuesTypeList["VARNAME"];
                string urn = GetUrnConcept(questId, cstOne, cstTwo);
                if (urn.Length > 0)
                {
                    ReportedAttributeType rat = atv.ReportedAttributeList.GetReportedAttributeOfConcept("CONCEPT_URN");
                    if (rat != null)
                        rat.InternationalizedValue.UpdateDefaultText(urn);
                    if (urnCodelist.Length > 0)
                    {
                        rat = atv.ReportedAttributeList.GetReportedAttributeOfConcept("CODELIST_URN");
                        if (rat != null)
                            rat.InternationalizedValue.UpdateDefaultText(urnCodelist);
                    }
                }
            }

        }

        private string GetUrnConcept(string questId, ConceptSchemeType cstOne, ConceptSchemeType cstTwo)
        {
            string urn = GetUrnConcept(questId, cstOne);
            if (urn.Length == 0)
                urn = GetUrnConcept(questId, cstTwo);
            return urn;
        }

        private string GetUrnConcept(string questId, ConceptSchemeType cst)
        {
            string urn = string.Empty;

            foreach (ConceptType c in cst.ConceptList)
            {
                Annotation a = c.AnnotationList.GetAnnotationType("QUEST_PRINT");
                if (a != null)
                {
                    List<string> questList = GetQidList(a.Title);
                    foreach (string s in questList)
                    {
                        if (s == questId)
                        {
                            if (c.CoreRepresentation.IsFilled)
                                urnCodelist = UrnManager.FullIdToUrn(SdmxArtefactType.CodeLists, c.CoreRepresentation.FullIdent);
                            return UrnManager.FullIdToUrn(SdmxArtefactType.Concepts, cst.FullIdent, c, null);
                        }
                    }
                }
            }
            return urn;
        }


        public List<string> GetQidList(string questIdDisplay)
        {
            List<string> listId = new List<string>();

            if (questIdDisplay != null && questIdDisplay.Length > 0)
            {
                // Get groups of questions
                string[] arrGrp = questIdDisplay.Split('_');

                foreach (string sRangeId in arrGrp)
                {
                    string[] arrSeparatorId = sRangeId.Split('/');
                    foreach (string sep in arrSeparatorId)
                    {
                        string[] arrIntervalId = sep.Split('-');
                        bool inInterval = false;
                        foreach (string inter in arrIntervalId)
                        {
                            GetId(listId, inter, inInterval);
                            inInterval = true;
                        }

                    }

                }
            }

            return listId;
        }

        private void GetId(List<string> listId, string tag, bool interval)
        {

            // two char, if interval create all val
            if (tag.Length == 2)
            {
                if (interval)
                {
                    int start = Convert.ToInt32(listId[listId.Count - 1].Substring(3, 2));
                    int end = Convert.ToInt32(tag);
                    string baseId = listId[listId.Count - 1].Substring(0, 3);
                    for (int i = start + 1; i <= end; i++)
                    {
                        listId.Add(baseId + i.ToString().PadLeft(2, '0'));
                    }

                }
                else if (listId.Count > 0)
                    listId.Add(listId[listId.Count - 1].Substring(0, 3) + tag);
            }
            else
            {
                string[] arr = tag.Split('.');
                if (arr.Length == 1)
                {
                    listId.Add(arr[0].PadRight(5, '0'));
                }
                else
                {
                    string s = arr[1].Length != 2 ? arr[1].Substring(0, 1) + "0" : arr[1];
                    s = arr[0] + s;
                    listId.Add(s.PadLeft(5, '0'));
                }

            }

            // 3, 4,5 chars without period, create new id

        }
    }
}
